package com.wnzt.baszh.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.wnzt.baszh.dao.ISystemMaintenDao;
import com.wnzt.baszh.entity.Tsysuser;
import com.wnzt.baszh.util.common.ConstantsUtils;
import com.wnzt.baszh.util.common.CryptoUtil;
import com.wnzt.baszh.util.common.DateUtil;
import com.wnzt.baszh.util.common.MD5Util;
import com.wnzt.baszh.util.common.PageHelper;
import com.wnzt.baszh.util.common.PageHelperService;

@Repository
public class SystemMaintenDaoImpl extends PageHelperService implements ISystemMaintenDao {
	
	@Autowired
	private JdbcTemplate jdbctemplate;
	
	Configuration conf = null;  
    ServiceRegistry st = null;  
    SessionFactory sf = null;  
    Session sess = null;  
    Transaction tx = null;
	/**
	 * 分页查询图片分类模板
	 */
	@Override
	public PageHelper getImageTempMainData(int page, int rows) {
		return this.queryMssqlSplitPageByProc("*", "SZH_BLLXMX", "1=1", "fpx", 1, "fpx", page, rows);
	}

	/**
	 * 添加图片分类模板
	 */
	@SuppressWarnings("deprecation")
	@Override
	public Integer AddImageTempMain(Map<String, Object> map) {
		
		String FCODE = (String) map.get("FCODE"); //图片分类模块编号
		String FNAME = (String) map.get("FNAME"); //图片分类模块名称
		String FPX = (String) map.get("FPX");	//排序号
		String ftype = (String) map.get("FTYPE");	//助记词
		
		String insertReturnSql="insert into SZH_BLLXMX(FCODE,FNAME,FTYPE,FPX) Values('"+FCODE+"','"+FNAME+"','"+ftype+"',"+FPX+") select @@identity";
		Integer returnId = this.jdbctemplate.queryForInt(insertReturnSql);
		
		return returnId;
	}

	/**
	 * 修改图片分类模板
	 */
	@Override
	public void EditImageTempMain(Map<String, Object> map) {
		
		String FCODE = (String) map.get("FCODE"); //图片分类模块编号
		String FNAME = (String) map.get("FNAME"); //图片分类模块名称
		Integer FPX = Integer.valueOf((String) map.get("FPX"));	//排序号
		String FTYPE = (String) map.get("FTYPE");	//助记词
		Integer FID = Integer.valueOf((String) map.get("FID"));	//模板id
		
		String updateSql = "update SZH_BLLXMX set FCODE='"+FCODE+"',FNAME='"+FNAME+"',FTYPE='"+FTYPE+"',FPX="+FPX+" where FID="+FID+"";
		this.jdbctemplate.execute(updateSql);
		
	}

	/**
	 * 删除图片模板
	 */
	@Override
	public void DeleteImageTempMain(String fID) {
		String deleteSql = "delete from SZH_BLLXMX where FID="+fID+"";
		this.jdbctemplate.execute(deleteSql);
	}

	/**
	 * 查询所有图片模板
	 */
	@Override
	public List CheckAllImageTempMain() {
		String querySql = "select * from SZH_BLLXMX";
		List list = this.jdbctemplate.queryForList(querySql);
		return list;
	}

	/**
	 * 保存水印
	 */
	@Override
	public Map<String, Object> SaveWatermark(Map<String, Object> map) {
		int mark=0;
		this.jdbctemplate.execute("truncate table szh_sy"); //清楚水印表中的内容
		Map<String, Object> reMap = new HashMap<String, Object>();
		String insertSql = " insert into szh_sy(FNR,FGD,FKD,FTMD,FFX,FZT,FDX,FYS,FJC,FYY,FWZ,FISUSE) Values('"+map.get("FNR")+"',"
				+ ""+map.get("FGD")+","+map.get("FKD")+","+map.get("alpa")+","+map.get("angle")+","+map.get("FZT")+","
				+ ""+map.get("FDX")+",'"+map.get("FYS")+"',"+map.get("weight")+","+map.get("shadow")+","+map.get("position")+","+map.get("isuse")+")";
		this.jdbctemplate.execute(insertSql);
		mark ++;
		if (mark>0) {
			reMap.put("success", true);
		}else {
			reMap.put("success", false);
		}
		return reMap;
	}

	/**
	 * 加载水印
	 */
	@Override
	public List<Map<String, Object>> CheckWatermark() {
		String querySql = "select * from szh_sy";
		List list = this.jdbctemplate.queryForList(querySql);
		return list;
	}
	
	@Override
    public PageHelper findUsers(Map<String, Object> params) {
        StringBuilder condition = new StringBuilder(" 1=1 ");
        if (params.containsKey("name")) {
            String name = (String) params.get("name");
            condition.append(" and s.fname like '%" + name + "%'");
        }
        if (params.containsKey("nameNum")) {
            String nameNum = (String) params.get("nameNum");
            condition.append(" and s.fcode like '%" + nameNum + "%'");
        }
        if (params.containsKey("department")) {
            String department = (String) params.get("department");
            condition.append(" and s.ftykh = '" + department + "'");
        }
        if (params.containsKey("userGroup")) {
            String userGroup = (String) params.get("userGroup");
            condition.append(" and s.fgroupcode = '" + userGroup + "'");
        }
        if (params.containsKey("type")) {
            String type = (String) params.get("type");
            condition.append(" and s.fusertype = '" + type + "'");
        }
        if (params.containsKey("casn")) {
            String casn = (String) params.get("casn");
            condition.append(" and s.FNETCASN like '%" + casn + "%'");
        }
        //增加配置审核人员界面检索条件   工号或者姓名
        if (params.containsKey("nameOrNum")) {
            String nameOrNum = (String) params.get("nameOrNum");
            condition.append(" and (s.fcode = '" + nameOrNum + "' or s.fname='"+nameOrNum+"')");
        }
        
        //ip设置界面根据权限类型判断用户是否已限制权限 1： 全部   2：已限制   3：未限制
        if (params.containsKey("isLimit")) {
            String isLimit = (String) params.get("isLimit");
            if ("2".equals(isLimit)) {
                condition.append(" and s.fcode in(select fcode from SZH_UQXIP)");
            }else if ("3".equals(isLimit)) {
                condition.append(" and s.fcode not in(select fcode from SZH_UQXIP)");
            } 
            
        }
        //condition.append(" and isnull(s.fifadmin,0)<>'1' ");//排除管理员
        //condition.append("  and EXISTS (SELECT * from szh_system a where a.fsysflag=s.fsysflag)");
       
        int page = Integer.parseInt((String) params.get("page"));
        int rows = Integer.parseInt((String) params.get("rows"));
        
        String columns = "s.fid,s.fnetcasn, s.fgroupcode, s.ftyyhbh, s.fname, s.fcode, s.ftykh, s.fksname, s.ffzbh, s.ffzname, g.fname as fgroupname, s.fusertype, s.fphone, s.fbqdm, s.fbqmc, t.ftypename, s.fsysflag";
        String tables = "tsysuser as s left join tgroup as g on s.fgroupcode = g.fcode left join tusertype as t on s.fusertype = t.fusertype";
        PageHelper pageHelper = this.queryMssqlSplitPageByProc(columns, tables, condition.toString(), "s.fid", 1, "s.fid", page, rows);
        return pageHelper;
    }

    @Override
    public List<Map<String, Object>> findUsername() {
        String sql = "select ftygh, fname from tdoctor";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findDepartments() {
        String sql = "select ftykh, fksname from tworkroom where ftype='2' order by fpx";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;

    }

    @Override
    public List<Map<String, Object>> findDepartmentsWithoutQy(Map<String, Object> params) {
        String fworkroom = (String) params.get("fworkroom");
        fworkroom = fworkroom.replaceAll("\"", "\'");
        String sql = "select ftykh, fksname from tworkroom where ftype='2' and ftykh not like '%QY%' and ftykh in (" + fworkroom + ") order by fpx";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;

    }
    
    @Override
    public List<Map<String, Object>> findBQ() {
        String sql = "select * from this_bqdmk";
        List<Map<String,Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }
    
    @Override
    public List<Map<String, Object>> findGroups() {
        String sql = "select ffzbh, ffz from basz_basnbfz";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findRoles() {
        String sql = "select fcode, fname from tgroup g where EXISTS (SELECT * from szh_system a where a.fsysflag=g.fsysflag)";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    @Transactional
    public void addUser(Map<String, Object> params) throws Exception {
        String nameNum = "", name = "",casn="", role = "", resName = "", password = "", userType = "", departNum = "", depart = "", groupNum = "", group = "", phone = "", workrooms = "", districtNum = "", district = "", districts = "";
        
        if (params.containsKey("nameNum")) {
            nameNum = (String) params.get("nameNum");
        }
        if (params.containsKey("name")) {
            name = (String) params.get("name");
        }
        if (params.containsKey("casn")) {
            casn = (String) params.get("casn");
        }
        if (params.containsKey("role")) {
            role = (String) params.get("role");
        }
        if (params.containsKey("resName")) {
            resName = (String) params.get("resName");
        }
        if (params.containsKey("password")) {
            password = (String) params.get("password");
        }
        if (params.containsKey("userType")) {
            userType = (String) params.get("userType");
        }
        if (params.containsKey("departNum")) {
            departNum = (String) params.get("departNum");
        }
        if (params.containsKey("depart")) {
            depart = (String) params.get("depart");
        }
        if (params.containsKey("groupNum")) {
            groupNum = (String) params.get("groupNum");
        }
        if (params.containsKey("group")) {
            group = (String) params.get("group");
        }
        if (params.containsKey("phone")) {
            phone = (String) params.get("phone");
        }
        if (params.containsKey("workrooms")) {
            workrooms = (String) params.get("workrooms");
        }
        if (params.containsKey("districtNum")) {
            districtNum = (String) params.get("districtNum");
        }
        if (params.containsKey("district")) {
            district = (String) params.get("district");
        }
        if (params.containsKey("districts")) {
            districts = (String) params.get("districts");
        }
        String sqlStr="select * from tsysuser where fnetcasn='"+casn+"'";
        List list=this.jdbctemplate.queryForList(sqlStr);
        if(list.size()>0){
        	throw new Exception("CA序列号已存在！");
        }
        password = CryptoUtil.aesEncrypt(password);
        
        StringBuffer sql = new StringBuffer("insert into tsysuser(fgroupcode, ftyyhbh, fcode, fpassword, fname, ftykh, fksname, fusertype, ffzbh, ffzname, fworkroom, fphone, fbqdm, fbqmc, fworkbq,fnetcasn) values ('");
        sql.append(role + "','" + nameNum + "','" + resName + "','" + password + "','" + name + "','" + departNum + "','" + depart + "','"  + userType + "','" + groupNum + "','" + group + "','" + workrooms + "','" + phone + "','" + districtNum + "','" + district + "','" + districts + "','" + casn + "')");
        
        jdbctemplate.execute(sql.toString());
    }

    @Override
    @Transactional
    public void updateUser(Map<String, Object> params) throws Exception {
        String id = "", nameNum = "", name = "",caSN = "", roleNum = "", resName = "", userType = "", departNum = "", depart = "", groupNum = "", group = "", phone = "", workrooms = "", districtNum = "", district = "", districts = "";
        
        if (params.containsKey("id")) {
            id = (String) params.get("id");
        }
        if (params.containsKey("nameNum")) {
            nameNum = (String) params.get("nameNum");
        }
        if (params.containsKey("caSN")) {
        	caSN = (String) params.get("caSN");
        }
        if (params.containsKey("name")) {
            name = (String) params.get("name");
        }
        if (params.containsKey("roleNum")) {
            roleNum = (String) params.get("roleNum");
        }
        if (params.containsKey("resName")) {
            resName = (String) params.get("resName");
        }
        if (params.containsKey("userType")) {
            userType = (String) params.get("userType");
            if ("medicalRoom".equals(userType)) {  // medicalRoom 表示是病案室人员，此时取用户分组更新数据
                if (params.containsKey("groupNum")) {
                    groupNum = (String) params.get("groupNum");
                }
                if (params.containsKey("group")) {
                    group = (String) params.get("group");
                }
            }
        }
        if (params.containsKey("departNum")) {
            departNum = (String) params.get("departNum");
        }
        if (params.containsKey("depart")) {
            depart = (String) params.get("depart");
        }
        if (params.containsKey("phone")) {
            phone = (String) params.get("phone");
        }
        if (params.containsKey("workrooms")) {
            workrooms = (String) params.get("workrooms");
        }
        if (params.containsKey("districtNum")) {
            districtNum = (String) params.get("districtNum");
        }
        if (params.containsKey("district")) {
            district = (String) params.get("district");
        }
        if (params.containsKey("districts")) {
            districts = (String) params.get("districts");
        }
        String sqlStr="select * from tsysuser where fnetcasn='"+caSN+"' and fid !="+id;
        List list=this.jdbctemplate.queryForList(sqlStr);
        if(list.size()>0){
        	throw new Exception("CA序列号已存在！");
        }
        String sql = "update tsysuser set fgroupcode = '" + roleNum + "', ftyyhbh = '" + nameNum + "', fcode = '" + resName + "', fname = '" + name + "', ftykh = '" + departNum + "', fksname = '" + depart + "', fusertype = '" + userType + "', ffzbh = '" + groupNum + "', ffzname = '" + group + "', fphone = '" + phone + "', fworkroom = '" + workrooms + "', fbqdm = '" + districtNum + "', fbqmc = '" + district + "', fworkbq = '" + districts + "' where fid = '" + id + "'";
        jdbctemplate.update(sql);
    }

    @Override
    @Transactional
    public void deleteUser(String idStr) {
        String id = idStr.substring(0, idStr.length() - 1);
        String sql = "delete from tsysuser where fid in (" + id + ")";
        jdbctemplate.execute(sql);
    }

    @Override
    public List<Map<String, Object>> findRoleList() {
        String sql = "select fid, fcode, fname, fsystemname = dbo.fn_ConcatSystems(fcode) from tgroup t where EXISTS (SELECT * from szh_system a where a.fsysflag=t.fsysflag) ";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findModules() {
        String sql = "select fid, fcode, fname from tright where fsysflag = '1'";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }
    
    @Override
    public List<Map<String, Object>> findModulesBySys(String fsysflag) {
        /*String sql = "select fid, fcode, fname from tright where fsysflag = '"+fsysflag.trim()+"'";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;*/
        
        StringBuffer sb = new StringBuffer(" 1=1 ");
    	if(ConstantsUtils.isNotBank(fsysflag)){//浏览人
			if(fsysflag.equals("0")){
				sb.append(" ");
			}else{
				sb.append(" and fsysflag = '"+ fsysflag.trim() +"' ");
			}
		} 
        String sql = "select fid, fcode, fname from tright "
        		+ " where "+sb.toString();
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    @Transactional
    public void addRole(Map<String, Object> params) {
        String roleNum = "", roleName = "", module = ""; 
        
        if (params.containsKey("roleNum")) {
            roleNum = (String) params.get("roleNum");
        }
        if (params.containsKey("roleName")) {
            roleName = (String) params.get("roleName");
        }
        if (params.containsKey("module")) {
            module = (String) params.get("module");
        }
        
        StringBuilder sql = new StringBuilder("insert into tgroup(fcode, fname, fsysflag) values ('");
        sql.append(roleNum + "','" + roleName + "','4')");
        jdbctemplate.execute(sql.toString());
        
        String[] moduleArray = module.split(",");
        for (int i = 0; i < moduleArray.length; i++) {
            String moduleSql = "insert into tgroupright(fgroupcode, frightcode) values ('" + roleNum + "','" + moduleArray[i] + "')";
            jdbctemplate.execute(moduleSql);
        }
    }

    /**
     * 更新采用的是先删除再添加的方法
     */
    @Override
    @Transactional
    public void updateRole(Map<String, Object> params, Map<String, Object> roleParams) {
        if (params.containsKey("id")) {
            String id = (String) params.get("id");
            deleteRole(id + ",");  // 删除角色信息  添加逗号是为了在删除方法内能正确得到id
            addRole(params); // 添加角色
            saveRoleModule(roleParams);
        }
    }

    @Override
    @Transactional
    public void deleteRole(String idStr) {
        String id = idStr.substring(0, idStr.length() - 1);
        String querySql = "select fcode from tgroup where fid in (" + id + ")";
        List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);       
        for (Map<String, Object> map : list) {
            String deleteSql = "delete from tgroupright where fgroupcode = '" + map.get("fcode") + "'";
            jdbctemplate.execute(deleteSql);
        }
        String sql = "delete from tgroup where fid in (" + id + ")";
        jdbctemplate.execute(sql);
    }
    
    @Override
    public List<Map<String, Object>> findMenuListBySysAndModule(String fsysflag, String module) {
        String sql = "SELECT t.fid AS id,t.fname AS text,t.fimage AS iconCls,t.fviewid AS attributes,t.fcode,t.ffather, t.fhavechildren, t.fhavefather, CASE WHEN r.fmenucode IS NOT NULL AND t.fhavechildren = 0 THEN 1 ELSE 0 END checked "+
                "FROM TMENU t LEFT JOIN TRIGHTMENU r ON t.fcode=r.fmenucode AND r.frightcode = '"+module+"' WHERE t.fsysflag = '"+fsysflag+"' ORDER BY t.ffather,t.fpx;";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findMenuList(String fsysflag) {
        String sql = "SELECT t.fid AS id, t.fname AS text, t.fimage AS iconCls, t.fviewid AS attributes, t.fcode, t.ffather FROM TMENU t WHERE t.fsysflag = '"+fsysflag+"' ORDER BY t.ffather, t.fpx";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findModuleMenus(String module) {
        String sql = "select m.fid as id, m.ffather from tmenu as m inner join trightmenu as r on m.fcode = r.fmenucode where m.fhavechildren = '0' and r.frightcode = '" + module + "'";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    @Transactional
    public void addModule(Map<String, Object> params) {
        String moduleNum = "", moduleName = "", fsysflag = "";
        if (params.containsKey("fsysflag")) {
            fsysflag = (String) params.get("fsysflag");
        }
        if (params.containsKey("moduleNum")) {
            moduleNum = (String) params.get("moduleNum");
        }
        if (params.containsKey("moduleName")) {
            moduleName = (String) params.get("moduleName");
        }
        
        StringBuilder sql = new StringBuilder("insert into tright(fcode, fname, fsysflag) values ('");
        sql.append(moduleNum + "','" + moduleName + "','"+fsysflag+"')");
        jdbctemplate.execute(sql.toString());
    }

    /**
     * 更新采用的是先删除再添加的方法
     */
    @Override
    @Transactional
    public void updateModule(Map<String, Object> params, Map<String,Object> moduleParams) {
        if (params.containsKey("id")) {
            String id = (String) params.get("id");
            deleteModule(id);  // 删除模块信息
            addModule(params);  // 添加模块
            saveModuleMenu(moduleParams); // 添加模块对应菜单
        }
    }

    @Override
    @Transactional
    public void deleteModule(String idStr) {
        String querySql = "select fcode from tright where fid = " + idStr + "";
        List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);       
        for (Map<String, Object> map : list) {
            String deleteSql = "delete from trightmenu where frightcode = '" + map.get("fcode") + "'";
            jdbctemplate.execute(deleteSql);
        }
        String sql = "delete from tright where fid = " + idStr + "";
        jdbctemplate.execute(sql);
    }

    @Override
    @Transactional
    public void saveModuleMenu(Map<String, Object> params) {
        String module = "" ,fsysflag = "";
        if (params.containsKey("fsysflag")) {
            fsysflag = (String) params.get("fsysflag");
        }
        if (params.containsKey("module")) {
            module = (String) params.get("module");
        }
        
        if(params.containsKey("nodes")) {
        
            String nodes = (String) params.get("nodes");
            String[] nodeArray = nodes.split(",");   // 菜单节点数组
            
            // 菜单节点字符串，sql查找使用
            String nodeStr = nodes.replace(",", "','");
            nodeStr = "'" + nodeStr + "'";   
            
            // 先删除数据表中多余的菜单节点
            String deleteSql = "delete from trightmenu where frightcode = '" + module + "' and fsysflag = '" + fsysflag + "' and fmenucode not in(" + nodeStr + ")";
            jdbctemplate.execute(deleteSql);
            
            String queryMenuSql = "SELECT fmenucode from TRIGHTMENU where frightcode = '" + module + "' and fsysflag = '" + fsysflag + "'";
            List<Map<String, Object>> menuList = jdbctemplate.queryForList(queryMenuSql);
            
            // 再写入新增的菜单节点
            for (String str : nodeArray) {
                boolean mark = true;
                for (Map<String, Object> map : menuList) {
                    if (str.equals(map.get("fmenucode"))) {
                        mark = false;
                        break;
                    }
                }
                if (mark) {
                    String sql = "insert into trightmenu(fmenucode, frightcode, fsysflag) values ('" + str + "','" + module + "', '" + fsysflag + "')";
                    jdbctemplate.execute(sql);
                }
            }
        } else {
        	// 当不包含nodes 时，表明用户没有勾选任何菜单，此时应删除对应模块下的所有菜单
        	String deleteSql = "delete from trightmenu where frightcode = '" + module + "' and fsysflag = '" + fsysflag + "'";
            jdbctemplate.execute(deleteSql);
        }
    }

    @Override
    @Transactional
    public void updatePassword(Map<String, Object> params) {
        String id = "", password = "";
        
        if (params.containsKey("id")) {
            id = (String) params.get("id");
        }
        if (params.containsKey("newPsw")) {
            password = (String) params.get("newPsw");
        }
        password = CryptoUtil.aesEncrypt(password);
        
        String sql = "update tsysuser set fpassword = '" + password +  "' where fid = '" + id + "'";
        jdbctemplate.update(sql);
        
    }

    @Override
    public List<Map<String, Object>> findModuleList(String name) {
        String sql = "SELECT r.fid AS id, r.fcode, r.fname AS text, r.fsysflag, (CASE WHEN g.fid IS NOT NULL and g.fid <> '' THEN 1 ELSE 0 END) as checked FROM tright as r LEFT JOIN tgroupright as g ON r.fcode = g.frightcode and g.fgroupcode = '" + name + "' ORDER BY r.fid ASC;";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    @Transactional
    public void saveRoleModule(Map<String, Object> params) {
        String role = "";
        if (params.containsKey("role")) {
            role = (String) params.get("role");
        }
        
        if (params.containsKey("childNodes")) {
            
            String childNodes = (String) params.get("childNodes");
            String[] childNodeArray = childNodes.split(",");
            
            String deleteSql = "delete from tgroupright where fgroupcode = '" + role + "'";
            jdbctemplate.execute(deleteSql);
            
            for (String str : childNodeArray) {
                String sql = "insert into tgroupright(fgroupcode, frightcode) values ('" + role + "','" + str + "')";
                jdbctemplate.execute(sql);
            }
        }
        
    }

    @Override
    public List<Map<String, Object>> getUsers(String id) {
        StringBuffer sql = new StringBuffer("select fid, ftyyhbh, fcode from tsysuser");
        if (ConstantsUtils.isNotBank(id)) {
            sql.append(" where fid <> '" + id +"'");
        }
        return this.jdbctemplate.queryForList(sql.toString());
    }

    @Override
    public List<Map<String, Object>> getRoles(String id) {
        StringBuffer sql = new StringBuffer("select fid, fcode from tgroup");
        if (ConstantsUtils.isNotBank(id)) {
            sql.append(" where fid <> '" + id +"'");
        }
        return this.jdbctemplate.queryForList(sql.toString());
    }

    @Override
    public List<Map<String, Object>> getModules(String id) {
        StringBuffer sql = new StringBuffer("select fid, fcode from tright");
        if (ConstantsUtils.isNotBank(id)) {
            sql.append(" where fid <> '" + id +"'");
        }
        return this.jdbctemplate.queryForList(sql.toString());
    }

    @Override
    public List<Map<String, Object>> findUserTypes() {
        String sql = "select fusertype, ftypename from tusertype";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findWorkRooms() {
        String sql = "SELECT fid, ftykh as id, fksname as text, fparent, fjb, fpx FROM tworkroom WHERE ftype = '2' ORDER BY fjb, fpx";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findUserWorkRooms(String user) {
        String sql = "SELECT fworkroom FROM tsysuser WHERE fcode = '" + user + "'";
        List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
        if (list != null) {
        	String string = (String) list.get(0).get("fworkroom");
        	if (null != string && !"".equals(string)) {
        		string = string.replace("\"", "'");
        		String wrSql = "SELECT fid, ftykh as id, fksname as text, fparent, fjb, fpx FROM tworkroom WHERE ftype = '2' AND ftykh in (" + string + ") ORDER BY fjb, fpx";
        		return jdbctemplate.queryForList(wrSql);
        	}
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> findUserWorkroomsAndQy(String user) {
        String sql = "SELECT fworkroom FROM tsysuser WHERE ftyyhbh = '" + user + "'";
        String string = jdbctemplate.queryForObject(sql, String.class);
        if (null != string && !"".equals(string)) {
            string = string.replace("\"", "'");
            String wrSql = "SELECT fid, ftykh as id, fksname as text, fparent, fjb, fpx FROM tworkroom WHERE ftype = '2' AND (ftykh in (" + string + ") or ftykh='TZYQY') ORDER BY fjb, fpx";
            return jdbctemplate.queryForList(wrSql);
        }else{
        	return null;
        }
    }
    
    @Override
    public List<Map<String, Object>> findMenuContents(String menuCode) {
        if (null != menuCode && !"".equals(menuCode)) {
            String sql = "select fid, fmenucode, fcode, fcontent from tpagecontent where fmenucode = '" + menuCode + "' order by forder";
            return jdbctemplate.queryForList(sql);
        }
        return null;
    }

    @Override
    public int saveMenuContents(Map<String, Object> params) {
        
        String moduleCode = "", menuCode = "", contentCode = "";
        if (params.containsKey("moduleCode")) {
            moduleCode = (String) params.get("moduleCode");
        }
        if (params.containsKey("menuCode")) {
            menuCode = (String) params.get("menuCode");
        }
        if (params.containsKey("contentCode")) {
            contentCode = (String) params.get("contentCode");
        }
        
        String sql = "update trightmenu set fcontentright = '" + contentCode + "' where frightcode = '" + moduleCode + "' and fmenucode = '" + menuCode + "'";
        return jdbctemplate.update(sql);
        
    }

    @Override
    public List<Map<String, Object>> findCheckPC(Map<String, Object> params) {
        String moduleCode = "", menuCode = "";
        if (params.containsKey("moduleCode")) {
            moduleCode = (String) params.get("moduleCode");
        }
        if (params.containsKey("menuCode")) {
            menuCode = (String) params.get("menuCode");
        }
        
        String sql = "select fcontentright from trightmenu where frightcode = '" + moduleCode + "' and fmenucode = '" + menuCode + "'";
        return jdbctemplate.queryForList(sql);
    }
    
    /**
     * 保存数据到操作日志中
     */
    @Override
    public void saveLog(Map<String, Object> map) {
        String fprnAndTimes=(String) map.get("fprnAndTimes");//获取病案号和次数
        String fprnAndTimesArr []=fprnAndTimes.split("@");//得到病案号和次数的数据
        String description=(String) map.get("description");//描述
        String method=(String) map.get("method");//方法
        String usercode=(String) map.get("usercode");//用户信息
        
        String QueryUserInfo="select fname fusername,ftykh,ftyyhbh from Tsysuser where fcode='"+usercode+"'";
        Map map1=this.jdbctemplate.queryForMap(QueryUserInfo);
        String date=DateUtil.dateConvertToString(new Date(), "yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < fprnAndTimesArr.length; i++) {
            String insertLogSql="insert into TsystemLog(fprn,ftimes,fczy,ftyyhbh,fdate,ftype,fmethod,fsysflag) values("
                    + fprnAndTimesArr[i]+",'"+map1.get("fusername")+"','"+map1.get("ftyyhbh")+"','"+date+"','"+description+"','"
                    +method+"',1)";
            this.jdbctemplate.execute(insertLogSql);
        }
    }

    /**
     * 权限设置检查权限名称唯一性
     */
    @SuppressWarnings("rawtypes")
	@Override
    public List checkModuleName(String moduleName,String fsysflag) {
        String checkSql="select fname from tright where fsysflag='"+fsysflag+"' and fname <>'"+moduleName+"'";
        List list = this.jdbctemplate.queryForList(checkSql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findSystems() {
        String sql = "SELECT fcode as id, fname as text, fcode, fimage as iconCls, fsysflag from szh_system";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findSystemInfo() {
        String sql = "SELECT fsysflag , fname from szh_system order by fpx asc";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findDistricts() {
        String sql = "SELECT fid, fbqdm as id, fbqmc as text from this_bqdmk";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findDistrictList() {
        String sql = "SELECT fbqdm, fbqmc from this_bqdmk";
        return jdbctemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> findUserDistrict(String user) {
        String sql = "SELECT fworkbq FROM tsysuser WHERE fcode = '" + user + "'";
        String string = jdbctemplate.queryForObject(sql, String.class);
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        if (null != string && !"".equals(string)) {
            string = string.replace("\"", "'");
            String wrSql = "SELECT fid, fbqdm as id, fbqmc as text FROM this_bqdmk WHERE fbqdm in (" + string + ")";
            list = jdbctemplate.queryForList(wrSql);
        }
        return list;
    }

	@Override
	public List<Map<String, Object>> findUserNum(String number) {
		String sql = "select fgh from tdoctor where ftygh = '" + number + "'";
		
		List<Map<String, Object>> list = jdbctemplate.queryForList(sql);
		return list;
	}

	//查看所有借阅目的
	@Override
	public List<Map<String, Object>> findBorrwPurpose() {
		String querySql = "select fid as id, fname as text from szh_jymd order by fid";
		
		List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
		return list;
	}

	//查看所有借阅目的下的审核级别
	@Override
	public List<Map<String, Object>> findAuditLevel(Object collection) {
		String querySql = "select ROW_NUMBER() over (order by fid) as number, fid as id,fname as text,fshid,fremarks,fjymdid from SZH_SPJB where fjymdid="+String.valueOf(collection)+" order by fid";
		
		List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
		return list;
	}
	
	//保存审核级别
	@Override
	public Object saveAuditorLevel(Map<String, String> map) {
		boolean  b = true;
		String insertSql = "insert into SZH_SPJB(fjymdid,fshid,fname,fremarks) values('"+map.get("fjymdid")+"',"
				+ "'"+map.get("fshid")+"','"+map.get("fname")+"','"+map.get("fremarks")+"')";
		try {
			jdbctemplate.execute(insertSql);
		} catch (Exception e) {
			b=false;
		}
		return b;
	}

	//修改审核级别
	@Override
	public Object updateAuditorLevel(Map<String, String> map) {
		boolean  b = true;
		String updateSql = "update SZH_SPJB set fshid='"+map.get("fshid")+"',fname='"+map.get("fname")+"',"
				+ "fremarks='"+map.get("fremarks")+"' where fid="+map.get("id");
		try {
			jdbctemplate.execute(updateSql);
		} catch (Exception e) {
			b=false;
		}
		return b;
	}

	//删除审核级别
	@Override
	public Object DeleteAuditorLevel(String childrenid, String parentid) {
		boolean  b = true;
		String deleteSql = "delete from SZH_SPJB where fjymdid="+parentid+" and fid >="+childrenid;
		try {
			jdbctemplate.execute(deleteSql);
		} catch (Exception e) {
			b=false;
		}
		return b;
	}

	//添加用户置审核级别
	@Override
	public Object addUserToAuditorLevel(String childrenid, String fcodes) {
		boolean  b = true;
		String fcodeStr = fcodes.substring(0, fcodes.length()-1);
		String updateSql = "update SZH_SPJB set fgh='"+fcodeStr+"' where fid="+childrenid;
		try {
			jdbctemplate.execute(updateSql);
		} catch (Exception e) {
			b=false;
		}
		return b;
	}

	@Override
	public PageHelper loadConfigureUserSelect(String type, String nodeid,int page, int rows) {
		String fgh = "";
		if (!"0".equals(type)) { //表示为子类
			String queryGhs = "select fgh from SZH_SPJB where fid ="+nodeid;
			fgh = this.jdbctemplate.queryForObject(queryGhs, String.class);
		}
		String wheresql = " 1=1";
		String fields = "s.fid,s.fcode,s.fname,s.fksname,g.fname fgroupname";
		String table = " tsysuser s left join tgroup g on s.fgroupcode = g.fcode";
		if (fgh != null && !"".equals(fgh)) {
			wheresql += " and s.fcode in("+fgh+")";
		}else {
			wheresql += " and s.fcode in('')";
		}
		
		return this.queryMssqlSplitPageByProc(fields, table, wheresql, "s.fid", 1, "s.fid", page, rows);
	}

	@Override
	public PageHelper getUserAccessPermis(String fcode, int page, int rows) {
		String field = "u.FID,u.FCODE,u.FQXQSIP,u.FQXJSIP,u.FXTCODE,u.FQSRQ,u.FJSRQ,p.fname FQXXT";
		
		return this.queryMssqlSplitPageByProc(field, "SZH_UQXIP u inner join szh_system p on u.FXTCODE=p.fsysflag", " u.fcode='"+fcode+"'", "u.fid", 1, "u.fid", page, rows);
	}

	@Override
	public boolean saveAccessSettings(Map<String, String> map) {
		boolean b = true;
		String insertSql = "insert into SZH_UQXIP(FCODE,FQXQSIP,FQXJSIP,FQSRQ,FJSRQ,TYPE,FXTCODE) "
		        + "VALUES('"+map.get("fcode")+"','"+map.get("fqxqsip")+"','"+map.get("fqxjsip")+"',"
		                + "'"+map.get("fstartDate")+"','"+map.get("fendDate")+"',1,'"+map.get("fxtname")+"')"; 
		try {
			this.jdbctemplate.execute(insertSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	} 

	@Override
	public boolean updateAccessSettings(Map<String, String> map) {
		boolean b = true;
		String updateSql = "update SZH_UQXIP set FQXQSIP='"+map.get("fqxqsip")+"',FQXJSIP='"+map.get("fqxjsip")+"',"
		        + "FQSRQ='"+map.get("fstartDate")+"',FJSRQ='"+map.get("fendDate")+"',FXTCODE='"+map.get("fxtname")+"' where fid="+map.get("fid")+""; 
		try {
			this.jdbctemplate.execute(updateSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public Object getAllUserIp(String fcode, String fxtcode, String fid, String operation) {
	    String where = "";
	    if ("1".equals(operation)) {
            where = " and fid <>"+fid;
        }
		String querySql = "select FQXQSIP,FQXJSIP,CONVERT(varchar(100), FQSRQ, 23) as FQSRQ,CONVERT(varchar(100), FJSRQ, 23) as FJSRQ from SZH_UQXIP where fcode='"+fcode+"' and FXTCODE="+fxtcode+" and type=1"+where;
		
		List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public boolean DeleteAccessAdressById(String fid) {
		boolean b = true;
		String deleteSql = "delete from SZH_UQXIP where fid="+fid;
		try {
			this.jdbctemplate.execute(deleteSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public PageHelper findRoleListByNum(String nameNum, int page, int rows) {
		String field = "fid, fcode, fname";
		String whereSql = "1=1";
		if (ConstantsUtils.isNotBank(nameNum)) {
			whereSql += " and fcode='"+nameNum+"' or fname like '%"+nameNum+"'";
		}
		
		return this.queryMssqlSplitPageByProc(field, "tgroup", whereSql, "fid", 1, "fid", page, rows);
	}

	@Override
	public PageHelper getBorrowPurpose(int page, int rows) {
		return this.queryMssqlSplitPageByProc("*", "SZH_JYMD", "1=1", "FID", 1, "FID", page, rows);
	}

	@Override
	public Object addBorrowPurpose(String fJYMDBH, String fNAME, String ftype) {
		boolean b = true;
		String addSql = "insert into SZH_JYMD(FJYMDBH,FNAME,FTYPE) VALUES('"+fJYMDBH+"','"+fNAME+"','"+ftype+"')";
		try {
			this.jdbctemplate.execute(addSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public Object editBorrowPurpose(String fID, String fJYMDBH, String fNAME, String ftype) {
		boolean b = true;
		String updateSql = "update SZH_JYMD set fJYMDBH='"+fJYMDBH+"',FNAME='"+fNAME+"',FTYPE='"+ftype+"' where fid="+fID;
		try {
			this.jdbctemplate.execute(updateSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public Object DeleteBorrowPurpose(String fID) {
		boolean b = true;
		String deleteSql = "delete from SZH_JYMD where fid="+fID;
		String deleteSql2 = "delete from SZH_JYMDYY where fjymdid="+fID;
		try {
			this.jdbctemplate.execute(deleteSql);
			this.jdbctemplate.execute(deleteSql2);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public Object CheckAllBorrowPurpose() {
		String querySql = "select FJYMDBH,FNAME from SZH_JYMD";
		List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public Object deletePersonalizedTemple(String fid) {
		boolean b = true;
		//删除模板表中的记录  SZH_XSMBSZ
		String deleteSql = "delete from SZH_XSMBSZ where fid="+fid;
		
		//删除模板明细表中对应的记录  SZH_MBDETAIL
		String deletesql = "delete from SZH_MBDETAIL where FFLFZBH="+fid;
		try {
			jdbctemplate.execute(deletesql);
			jdbctemplate.execute(deleteSql);
		} catch (Exception e) {
			b = false;
		}
		return b;
	}

	@Override
	public Object getTreeData(String fid) {
		String querySql = "";
		if (ConstantsUtils.isNotBank(fid)) {
			querySql = "SELECT ROW_NUMBER() over (order by s.fpx) as number, m.FNAME 'text',s.fpx 'id','#' parent FROM SZH_MBDETAIL s INNER JOIN SZH_BLLXMX m ON s.FCODE = m.FCODE "
					+ "WHERE s.FFLFZBH="+fid+" order by fpx";
		}else {
			querySql = "SELECT ROW_NUMBER() over (order by fpx) as number, FNAME 'text',fpx 'id','#' parent FROM SZH_BLLXMX order by fpx";
		}
		List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public Object saveAccessSet(String groupCode, int right, String time,int timeUnit) {
		boolean b = true;
		String deleteSql = "delete from SZH_GROUPLLQXINFO where FGROUPCODE='"+groupCode+"'";
		String insertSql = "insert into SZH_GROUPLLQXINFO(FLLQXTYPE,FLLQXSJ,FLLQXSJDW,FGROUPCODE) "
				+ "VALUES("+right+","+time+","+timeUnit+",'"+groupCode+"')";
		try {
			jdbctemplate.execute(deleteSql);
			jdbctemplate.execute(insertSql);
		} catch (Exception e) {
			
			b = false;
		}
		return b;
	}

	@Override
	public Object getAccessSet(String groupCode) {
		List<Map<String, Object>> list = jdbctemplate.queryForList("select * from SZH_GROUPLLQXINFO where FGROUPCODE='"+groupCode+"'");
		return list;
	}

	@Override
	public Object getImgTemple(String groupCode) {
		String querySql = "select FID id,FIMGMBNAME text,FISDEFAULT from SZH_IMGMBMAIN where FGROUPCODE='"+groupCode+"' and FSYSFLAG=0";
		List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public Object getFieldTemple(String groupCode) {
		String querySql = "select FID id,FFIELDMBNAME text,FISDEFAULT from SZH_FIELDMBMAIN where FGROUPCODE='"+groupCode+"' and FSYSFLAG = 0";
		List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public Object findImgTemoleDetail(String fid) {
		String querySql = "select FID,FNAME fname,'' val from SZH_BLLXMX";
		if (fid != null) {
			querySql = "select a.FID,a.FNAME fname,t.FMBVALUE val from SZH_IMGMBDETAIL t inner join SZH_BLLXMX a on t.FMBID=a.fid where FIMGMBID="+fid;
		}
		List<Map<String, Object>> list = jdbctemplate.queryForList(querySql); 
		return list;
	}

	@Override
	public Object saveImgTemple(Map<String, String> map) {
		boolean b = true;
		//将图片分类模板fid以逗号分割放置数组中
		String [] fid = map.get("ids").split(","); 
		
		//将图片分类阅读、打印和导出 以逗号分割放置数组中
		String [] val = map.get("value").split(",");
		
		if (ConstantsUtils.isNotBank(map.get("imgid"))) {  //判断如果图片分类模板不为空执行的操作
			for (int i = 0; i < val.length; i++) {
				String updateSql = "update SZH_IMGMBDETAIL set FMBVALUE='"+val[i]+"' where FIMGMBID ='"+map.get("imgid")+"' and FMBID='"+fid[i]+"'";
				try {
					jdbctemplate.execute(updateSql);
				} catch (Exception e) {
					b=false;
				}
			}
			 String updateSql = "update SZH_IMGMBMAIN set FIMGMBNAME='"+map.get("name")+"' where FID="+map.get("imgid");
			 jdbctemplate.execute(updateSql);
		}else {
			String returnSql = "insert into SZH_IMGMBMAIN(FIMGMBNAME,FGROUPCODE,FISDEFAULT,FSYSFLAG) VALUES('"+map.get("name")+"','"+map.get("fcode")+"',0,0) SELECT @@IDENTITY";
			try {
				String mbFid = this.jdbctemplate.queryForObject(returnSql, String.class);
				
				//开启hibernate批量出来数据存储，防止数据量大 影响性能
				for (int i = 0; i < fid.length; i++) {
					String inertSql = "insert into SZH_IMGMBDETAIL(FIMGMBID,FMBVALUE,FMBID) VALUES('"+mbFid+"','"+val[i]+"','"+fid[i]+"')";
					jdbctemplate.execute(inertSql);
				}
			} catch (Exception e) {
				b = false;
			} 
		}
		return b;
	}

	@Override
	public List<Map<String, Object>> getFieldTree() {
		String querySql = "select FID 'id',FFIELDCODE, FFIELDNAME 'text' from TQUERYSELFCONDITION WHERE FTABLENAME='住院信息'";
		List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
		return list;
	}

	@Override
	public Object saveFieldTemple(Map<String, String> map) {
		boolean b = true;
		//获取字段分类模板字段编号字符串存之数组中
		String [] fieldCode = map.get("fieldCode").split(","); 
		
		//获取字段分类模板字段名称字符串存之数组中
		String [] fieldName = map.get("fieldName").split(",");
		
		if (ConstantsUtils.isNotBank(map.get("fieldMbId"))) {  //判断如果查询字段模板不为空执行的操作
			//根据字段分类模板id 删除，明细表中原来的数据
			String deleteSql = "delete from SZH_FIELDMBDETAIL where FFIELDMBID="+map.get("fieldMbId");
			jdbctemplate.execute(deleteSql);
			
			for (int i = 0; i < fieldCode.length; i++) {
				
				//重新执行从插入操作(重新执行插入在于   字段列表中字段顺序可能被打乱)
				String insertSql = "insert into SZH_FIELDMBDETAIL (FFIELDCODE,FFIELDNAME,FFIELDMBID,ALIGN,WIDTH) VALUES('"+fieldCode[i]+"'"
						+ ",'"+fieldName[i]+"',"+map.get("fieldMbId")+",'center',60)";
				try {
					jdbctemplate.execute(insertSql);
				} catch (Exception e) {
					b=false;
					break;   //终止操作  返回
				}
			}
			String updateSql="UPDATE SZH_FIELDMBMAIN SET FFIELDMBNAME='"+map.get("name")+"' WHERE FID="+map.get("fieldMbId");
			jdbctemplate.execute(updateSql);
		}else {
			String returnSql = "insert into SZH_FIELDMBMAIN(FGROUPCODE,FFIELDMBNAME,FSYSFLAG,FISDEFAULT) VALUES('"+map.get("fcode")+"','"+map.get("name")+"',0,0) SELECT @@IDENTITY";
			try {
				String mbFid = this.jdbctemplate.queryForObject(returnSql, String.class);
				
				//开启hibernate批量出来数据存储，防止数据量大 影响性能
				for (int i = 0; i < fieldCode.length; i++) {
					String insertSql = "insert into SZH_FIELDMBDETAIL (FFIELDCODE,FFIELDNAME,FFIELDMBID,ALIGN,WIDTH) VALUES('"+fieldCode[i]+"'"
							+ ",'"+fieldName[i]+"',"+mbFid+",'center',60)";
					try {
						jdbctemplate.execute(insertSql);
					} catch (Exception e) {
						b = false;
						break;   //终止操作  返回
					}
				}
			} catch (Exception e) {
				b = false;
			} 
		}
		return b;
	}

	@Override
	public Object findFieldTemoleDetail(String id) { 
		if (id == null) {
			return false;
		}else {
			String querySql = "select FID id,FFIELDNAME name ,FFIELDCODE,'true' checked from SZH_FIELDMBDETAIL where FFIELDMBID="+id+" order by fid";
			
			List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
			return list;
		}
	}

	@Override
	public void updateImgMbDefault(String id, String Groupfcode) {
		String updateSql = "update SZH_IMGMBMAIN set FISDEFAULT = null where FGROUPCODE='"+Groupfcode+"'";
		String updateDefSql = "update SZH_IMGMBMAIN set FISDEFAULT = 0 where fid="+id;
		
		//先清空列表中 FISDEFAULT字段值  ，再给系统默认选择项赋值  0
		try {
			jdbctemplate.execute(updateSql);
			jdbctemplate.execute(updateDefSql);
		} catch (Exception e) {
		}
	}

	@Override
	public void updateFiledMbDefault(String id, String Groupfcode) {
		String updateSql = "update SZH_FIELDMBMAIN set FISDEFAULT = null where FGROUPCODE='"+Groupfcode+"'";
		String updateDefSql = "update SZH_FIELDMBMAIN set FISDEFAULT = 0 where fid="+id;
		
		//先清空列表中 FISDEFAULT字段值  ，再给系统默认选择项赋值  0
		try {
			jdbctemplate.execute(updateSql);
			jdbctemplate.execute(updateDefSql);
		} catch (Exception e) {
		}
	}

	@Override
	public Object searchFieldByName(String value) {
		String querySql = "SELECT TOP 1 * FROM TQUERYSELFCONDITION WHERE FTABLENAME='住院信息' AND FFIELDNAME LIKE '"+value+"%'";
		
		Map<String, Object> map = this.jdbctemplate.queryForMap(querySql);
		return map;
	}

    @Override
    public boolean saveSystemSettings(Map<String, String> map) {
        boolean b = true;
        
        String insertSql = "insert into SZH_UQXIP(FCODE,FQXQSIP,FQXJSIP,FQSRQ,FJSRQ,TYPE) VALUES('"+map.get("fxtname")+"','"+map.get("fqxqsip")+"','"+map.get("fqxjsip")+"','"+map.get("fstartDate")+"','"+map.get("fendDate")+"',0)"; 
        try {
            this.jdbctemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public boolean updateSystemSettings(Map<String, String> map) {
        boolean b = true;
        
        String updateSql = "update SZH_UQXIP set FQXQSIP='"+map.get("fqxqsip")+"',FQXJSIP='"+map.get("fqxjsip")+"',"
                + "FQSRQ='"+map.get("fstartDate")+"',FJSRQ='"+map.get("fendDate")+"',FCODE='"+map.get("fxtname")+"' where fid="+map.get("fid")+""; 
        try {
            this.jdbctemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public PageHelper loadSystemAccessAdr(int page, int rows) {
        String fields = "u.FQSRQ,u.FJSRQ,u.FID,u.FCODE,u.FQXQSIP,u.FQXJSIP,p.fname FJZXT";
        String table = "SZH_UQXIP u inner join szh_system p on u.fcode=p.fsysflag";
        
        return this.queryMssqlSplitPageByProc(fields, table, "u.type=0", "u.fid", 1, "u.fid", page, rows);
    }

    @Override
    public Object getAllSystemIp(String fcode, String fid, String operation) {
        String where = "";
        if ("1".equals(operation)) {
            where = " and fid <>"+fid;
        }
        String querySql = "select FQXQSIP,FQXJSIP,CONVERT(varchar(100), FQSRQ, 23) as FQSRQ,CONVERT(varchar(100), FJSRQ, 23) as FJSRQ from SZH_UQXIP where fcode='"+fcode+"' and type=0"+where;
        
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public PageHelper loadReasonsApplitData(String fjymdid, int page, int rows) {
        return this.queryMssqlSplitPageByProc("*", "SZH_JYMDYY", "fjymdid="+fjymdid, "fid", 1, "fid", page, rows);
    }

    @Override
    public Object addReasonsApplit(String fjymdid, String fjymdyymc, String fjymdyybh) {
        boolean b = true;
       
        String insertSql = "insert into SZH_JYMDYY(FJYMDID,FJYMDYYBH,FJYMDYYMC) VALUES('"+fjymdid+"','"+fjymdyybh+"','"+fjymdyymc+"')"; 
        try {
            this.jdbctemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object editReasonsApplit(String fid, String fjymdyymc, String fjymdyybh) {
        boolean b = true;
        
        String updateSql = "update SZH_JYMDYY set FJYMDYYBH='"+fjymdyybh+"',FJYMDYYMC='"+fjymdyymc+"' where fid="+fid; 
        try {
            this.jdbctemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object CheckAllReasonsApplit(String fjymdid) {
        String querySql = "select * from SZH_JYMDYY where FJYMDID="+fjymdid;
        
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object DeleteReasonsApplit(String fid) {
        boolean b = true;
        
        String deleteSql = "delete from SZH_JYMDYY where fid="+fid; 
        try {
            this.jdbctemplate.execute(deleteSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object loadApplyType() {
        String querySql = "select FID,FNAME from SZH_JYMD";
        
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object loadUsers() {
        String querySql = "select FCODE,FNAME from TSYSUSER";
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object loadExamineLevel(String fid) {
        String querySql = "select ROW_NUMBER() over (order by fpx) as orderV,FID,FNAME,FPX,"
                + "(case when fpx = (SELECT max(fpx) from szh_spjb) then '结束' else '中间' end) fhj from SZH_SPJB where FJYMDID="+fid;
        
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object loadExaminePersion(String fid) {
        String querySql = "select p.FID, s.fcode,s.fname,t.fname fgroupname,p.fspks,p.FSPKSNAME fksname from SZH_SPJBRY p inner join TSYSUSER s on p.fusercode=s.fcode "
                + "inner join tgroup t on s.fgroupcode=t.fcode where p.fspjbid="+fid;
        
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object checkSpName(String type, String newName, String oldName) {
        String where = "where";
        if ("1".equals(type)) {  //修改名称
            where += " fname<>'"+oldName+"' and fname='"+newName+"'";
        }else {
            where += " fname='"+newName+"'";
        }
        String querySql = "select * from SZH_SPJB "+where;
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object addExamineLevel(String examineName, String applyTypeId) {
        boolean b = true;
        String insertSql = "insert into SZH_SPJB(FJYMDID,FNAME) VALUES("+applyTypeId+",'"+examineName+"') SELECT @@IDENTITY"; 
        try {
            //插入的时候返回fid 更新fpx字段
            int fpx = this.jdbctemplate.queryForObject(insertSql, Integer.class);
            
            String updateSql = "update SZH_SPJB set fpx="+fpx+" where fid="+fpx;
            //将fid值更新带列 fpx
            this.jdbctemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object editExamineLevel(String examineName, String fid) {
        boolean b = true;
        String updateSql = "update SZH_SPJB set FNAME='"+examineName+"' where FID="+fid; 
        try {
            this.jdbctemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public void ExchangeFpx(String currentId, String currentFpx, String nextId, String nextFpx) {
        
        //根据id互换两行数据的fpx号
        String update1 = "update SZH_SPJB set fpx="+nextFpx+" where fid="+currentId;
        String update2 = "update SZH_SPJB set fpx="+currentFpx+" where fid="+nextId;
        
        try {
            this.jdbctemplate.execute(update1);
            this.jdbctemplate.execute(update2);
        } catch (Exception e) {
            System.out.println("交换失败");
        }
    }

    @Override
    public Object deleteExamineLevel(String fpx) {
        boolean b = true;
        String deleteSql = "delete from SZH_SPJB  where FPX="+fpx; 
        String delete = "delete from SZH_SPJBRY where fspjbid="+fpx;
        try {
            this.jdbctemplate.execute(deleteSql);
            this.jdbctemplate.execute(delete);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object deleteExaminePersion(String fid) {
        boolean b = true;
        String deleteSql = "delete from SZH_SPJBRY where FID="+fid; 
        try {
            this.jdbctemplate.execute(deleteSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object editExaminePersion(String fid, String persionCode, String fspks, String examineKsName) {
        boolean b = true;
        String updateSql = "update SZH_SPJBRY set FUSERCODE='"+persionCode+"',FSPKS='"+fspks+"',FSPKSNAME='"+examineKsName+"' where FID="+fid; 
        try {
            this.jdbctemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object addExaminePersion(String fspjbid, String persionCode, String fspks, String examineKsName) {
        boolean b = true;
        String insertSql = "insert into SZH_SPJBRY (FSPJBID,FUSERCODE,FSPKS,FSPKSNAME) VALUES("+fspjbid+",'"+persionCode+"','"+fspks+"','"+examineKsName+"')"; 
        try {
            this.jdbctemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object loadUserImgDataByTemp(Tsysuser userObject, String tempid) {
        String selectSql = "select b.*,a.FNAME from SZH_IMGMBDETAIL b "
                + "inner join SZH_BLLXMX a on a.FID=b.FMBID where b.FIMGMBID="+tempid+" order by FID";
        List<Map<String, Object>> list = jdbctemplate.queryForList(selectSql);
        
        //更新其他默认模板字段值
        String updateSql2 = "update SZH_IMGMBMAIN set FISDEFAULT = null where fgroupcode ='" + userObject.getFcode() + "' and FSYSFLAG = 1";
        this.jdbctemplate.execute(updateSql2);
        
        //更新用户个人默认模板
        String updateSql = "update SZH_IMGMBMAIN set FISDEFAULT = 0 where fid =" + tempid + " and FSYSFLAG = 1";
        this.jdbctemplate.execute(updateSql);
        
        //判断刚新建的模板下  图片分类不存在的情况下默认查询系统的模板
        if (list.size() == 0) {
            String checkSql = "select b.*,a.FNAME from SZH_IMGMBDETAIL b inner join SZH_IMGMBMAIN m on m.FID = b.FIMGMBID "
                    + "inner join SZH_BLLXMX a on a.FID=b.FMBID where m.FGROUPCODE='"+userObject.getFgroupcode()+"' and m.FSYSFLAG=0 and m.FISDEFAULT = 0 order by b.FID";
            list = this.jdbctemplate.queryForList(checkSql);
        }
        return list;
    }
    
    @Override
    public Object getUserImgShowTemple(Tsysuser userObject) {
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        
        //查找系统默认模板
        String checkSysTempSql = "select FID id,FIMGMBNAME text,FISDEFAULT,FSYSFLAG type from SZH_IMGMBMAIN where FSYSFLAG=0 and FISDEFAULT = 0 and FGROUPCODE='"+userObject.getFgroupcode()+"'";
        List<Map<String, Object>> listSys = this.jdbctemplate.queryForList(checkSysTempSql);
        
        //存在系统模板就放入list中
        if (listSys.size()>0) {
            list.add(listSys.get(0));
        }
        
        //查找用户默认模板
        String checkUserTempSql = "select FID id,FIMGMBNAME text,FISDEFAULT,FSYSFLAG type from SZH_IMGMBMAIN where FSYSFLAG=1 and FGROUPCODE ='"+userObject.getFcode()+"'";
        List<Map<String, Object>> listUser = this.jdbctemplate.queryForList(checkUserTempSql);
        
        list.addAll(listUser);
        return list;
    }

    @Override
    public Object getUserFieldShowTemple(Tsysuser userObject) {
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        
        //查找系统默认模板
        String checkSysFielsSql = "select FID id,FFIELDMBNAME text,FSYSFLAG type from SZH_FIELDMBMAIN where FSYSFLAG=0 and FISDEFAULT = 0 and FGROUPCODE='"+userObject.getFgroupcode()+"'";
        List<Map<String, Object>> listSys = this.jdbctemplate.queryForList(checkSysFielsSql);
        
        //存在系统模板就放入list中
        if (listSys.size()>0) {
            list.add(listSys.get(0));
        }
        
        //查找用户默认模板
        String checkUserFielsSql = "select FID id,FFIELDMBNAME text,FISDEFAULT,FSYSFLAG type from SZH_FIELDMBMAIN where FSYSFLAG=1 and FGROUPCODE ='"+userObject.getFcode()+"'";
        List<Map<String, Object>> listUser = this.jdbctemplate.queryForList(checkUserFielsSql);
        
        list.addAll(listUser);
        return list;
    }

    @Override
    public Object saveInAccessSet(Map<String, Object> map) {
        boolean b = true;
        String deleteSql = "delete from SZH_USERLLQXINFO where FUSERCODE='"+map.get("fusercode")+"'";
        String insertSql = "insert into SZH_USERLLQXINFO(FSHOWMODE,FSHOWCOLOR,NUMBERROW,FUSERCODE,PRINTPAPER) "
                + "VALUES("+map.get("imgShowMode")+","+map.get("imgShowcolor")+","+map.get("showNumberRow")+","
                        + "'"+map.get("fusercode")+"',"+map.get("printPaper")+")";
        try {
            jdbctemplate.execute(deleteSql);
            jdbctemplate.execute(insertSql);
        } catch (Exception e) {
            
            b = false;
        }
        return b;
    }

    @Override
    public List<Map<String, Object>> getFieldInTree(String fgroupcode) {
        String querySql = "SELECT f.FID id,FFIELDCODE FFIELDCODE,FFIELDNAME text FROM SZH_FIELDMBMAIN s INNER JOIN SZH_FIELDMBDETAIL f ON f.FFIELDMBID = s.FID where s.FGROUPCODE='"+fgroupcode+"' AND s.FISDEFAULT=0";
        List<Map<String, Object>> list = jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object getInAccessSet(String fcode) {
        String querySql = "select * from SZH_USERLLQXINFO where FUSERCODE='"+fcode+"'";
        List<Map<String, Object>> list = this.jdbctemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object saveInFieldTemp(Map<String, Object> map) {
        
        String fcode = (String) map.get("fcode"); //获取用户工号
        String name = (String) map.get("name");  //获取模板名称
        String mbid = (String) map.get("mbId");  //获取模板id
        
        int b=0;   //返回添加或修改 模板的fid
        
        //执行添加操作
        if ("".equals(mbid) || mbid == null) {
            String insertSql = "insert into SZH_FIELDMBMAIN(FGROUPCODE,FFIELDMBNAME,FSYSFLAG) VALUES('"+fcode+"','"+name+"',1) select @@identity";
            try {
                b = this.jdbctemplate.queryForObject(insertSql, Integer.class);
            } catch (Exception e) {
                return false;
            }
            
            
        }else {
            String updateSql = "update SZH_FIELDMBMAIN set FFIELDMBNAME='"+name+"' where fid="+mbid;
            try {
                this.jdbctemplate.execute(updateSql);
                b = Integer.parseInt(mbid);
                
            } catch (Exception e) {
                return false;
            }
        }
        return b;
    }

    @Override
    public Object saveInFieldTemple(Map<String, String> map) {
        boolean b = true;
        //获取字段分类模板字段编号字符串存之数组中
        String [] fieldCode = map.get("fieldCode").split(","); 
        
        //获取字段分类模板字段名称字符串存之数组中
        String [] fieldName = map.get("fieldName").split(",");
        
        //根据字段分类模板id 删除，明细表中原来的数据
        String deleteSql = "delete from SZH_FIELDMBDETAIL where FFIELDMBID="+map.get("fieldMbId");
        jdbctemplate.execute(deleteSql);
        
        for (int i = 0; i < fieldCode.length; i++) {
            
            //重新执行从插入操作(重新执行插入在于   字段列表中字段顺序可能被打乱)
            String insertSql = "insert into SZH_FIELDMBDETAIL (FFIELDCODE,FFIELDNAME,FFIELDMBID,ALIGN,WIDTH) VALUES('"+fieldCode[i]+"'"
                    + ",'"+fieldName[i]+"',"+map.get("fieldMbId")+",'center',60)";
            try {
                jdbctemplate.execute(insertSql);
            } catch (Exception e) {
                b=false;
                break;   //终止操作  返回
            }
        }
        return b;
    }

    @Override
    public Object loadUserFieldDataByTemp(int mbid, Tsysuser userObject) {
        String querySql = "select FFIELDCODE,FFIELDNAME name from SZH_FIELDMBDETAIL where FFIELDMBID="+mbid;
        List<Map<String, Object>> list  = this.jdbctemplate.queryForList(querySql);
        
      //更新其他默认模板字段值
        String updateSql2 = "update SZH_FIELDMBMAIN set FISDEFAULT = null where fgroupcode ='" + userObject.getFcode() + "' and FSYSFLAG = 1";
        this.jdbctemplate.execute(updateSql2);
        
        //更新用户个人默认模板
        String updateSql = "update SZH_FIELDMBMAIN set FISDEFAULT = 0 where fid =" + mbid + " and FSYSFLAG = 1";
        this.jdbctemplate.execute(updateSql);
        
        return list;
    }

    @Override
    public Object deleteFieldTemple(int mbid) {
        boolean b = true;
        String deleteMain = "delete from SZH_FIELDMBMAIN where fid="+mbid;
        String deleteDetail = "delete from SZH_FIELDMBDETAIL where FFIELDMBID="+mbid;
        try {
            this.jdbctemplate.execute(deleteMain);
            this.jdbctemplate.execute(deleteDetail);
        } catch (Exception e) {
            b = false;
            e.printStackTrace();
        }
        return b;
    }

    @Override
    public Object saveInImgTemp(Map<String, Object> map) {
        
        String fcode = (String) map.get("fcode"); //获取用户工号
        String name = (String) map.get("name");  //获取模板名称
        String mbid = (String) map.get("mbId");  //获取模板id
        
        int b=0;   //返回添加或修改 模板的fid
        
        //执行添加操作
        if ("".equals(mbid) || mbid == null) {
            
            //更新该用户下面的的图片分类主模板  FISDEFAULT为 null
            String updateSql = "update SZH_IMGMBMAIN set FISDEFAULT = null where FGROUPCODE = '"+fcode+"'";
            jdbctemplate.execute(updateSql);
            
            //执行插入用户默认模板
            String insertSql = "insert into SZH_IMGMBMAIN(FIMGMBNAME,FGROUPCODE,FISDEFAULT,FSYSFLAG) VALUES('"+name+"','"+fcode+"',0,1) select @@identity";
            try {
                b = this.jdbctemplate.queryForObject(insertSql, Integer.class);
            } catch (Exception e) {
                return false;
            }
            
            
        }else {
            String updateSql = "update SZH_IMGMBMAIN set FIMGMBNAME='"+name+"' where fid="+mbid;
            try {
                this.jdbctemplate.execute(updateSql);
                b = Integer.parseInt(mbid);
                
            } catch (Exception e) {
                return false;
            }
        }
        return b;
    }

    @Override
    public Object deleteInImgTemple(int mbid) {
        boolean b = true;
        String deleteMain = "delete from SZH_IMGMBMAIN where fid="+mbid;
        String deleteDetail = "delete from SZH_IMGMBDETAIL where FIMGMBID="+mbid;
        try {
            this.jdbctemplate.execute(deleteMain);
            this.jdbctemplate.execute(deleteDetail);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object saveInImgTemple(int mbid, String tmbidStr) {
        boolean b = true;
        
        //先删除该模板下的所有图片分类
        String deleteSql = "delete from SZH_IMGMBDETAIL where FIMGMBID="+mbid;
        this.jdbctemplate.execute(deleteSql);
        
        String [] tmbidArra = tmbidStr.split(",");
        
        //循环将图片分类插入
        for (String tmbid : tmbidArra) {
            
            String insertSql = "insert into SZH_IMGMBDETAIL(FIMGMBID,FMBID) VALUES("+mbid+","+Integer.parseInt(tmbid)+")";
            try {
                jdbctemplate.execute(insertSql);
            } catch (Exception e) {
                b = false;
                break;  //出现异常  跳出循环
            }
        }
        return b;
    }

	@Override
	public List<Map<String, Object>> findNetcaSN() {
		String sql="select t.* from szh_userca t where  t.fstop=0";//ca.FSERIALNUMBER not in (select u.fnetcasn  from tsysuser u) and
		return this.jdbctemplate.queryForList(sql);
	}

	@Override
	public List<Map<String, Object>> getSystem(Map<String, Object> param)
			throws Exception {
		return null;
	}

	@Override
	public void addSystem(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void editSystem(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void deleteSystem(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public List<Map<String, Object>> getMenuBySys(Map<String, Object> param)
			throws Exception {
		return null;
	}

	@Override
	public void addMenu(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void editMenu(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void deleteMenu(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public List<Map<String, Object>> getFunctionByMenu(Map<String, Object> param)
			throws Exception {
		return null;
	}

	@Override
	public void addFunction(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void editFunction(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public void deleteFunction(Map<String, Object> param) throws Exception {
		
	}

	@Override
	public Map<String, Object> getSystemByFcode(String code) throws Exception {
		return null;
	}

	@Override
	public Map<String, Object> getMenuByFcode(String code) throws Exception {
		return null;
	}

	@Override
	public List<Map<String, Object>> getAllBllx() {
		return this.jdbctemplate.queryForList("SELECT *,fid id,fname text FROM SZH_BLLX");
	}

	@Override
	public Map<String, Object> getBllxByID(String id) {
		return this.jdbctemplate.queryForMap("SELECT * FROM SZH_BLLX WHERE FID="+id);
	}

	@Override
	public List<Map<String, Object>> getAllBllxmx() {
		return this.jdbctemplate.queryForList("SELECT * FROM SZH_BLLXMX");
	}

	@Override
	public Map<String, Object> getBllxmxByID(String id) {
		return this.jdbctemplate.queryForMap("SELECT * FROM SZH_BLLXMX WHERE FID="+id);
	}

	@Override
	public List<Map<String, Object>> getAllBllxmxByType(String type) {
		return this.jdbctemplate.queryForList("SELECT * FROM SZH_BLLXMX WHERE FTYPE="+type);
	}

	@Override
	public List<Map<String, Object>> getAllBllxWithBllxmx() {
		return this.jdbctemplate.queryForList("SELECT t.*,l.FNAME BLLX FROM SZH_BLLXMX t LEFT JOIN SZH_BLLX l ON l.FCODE=t.FTYPE");
	}

}
